package server;

// This file contains material supporting section 3.7 of the textbook:
// "Object Oriented Software Engineering" and is issued under the open-source
// license found at www.lloseng.com 
import java.awt.event.MouseAdapter;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.Hashtable;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import model.CampaignPatern;
import model.CostumerModel;
import model.CustomerHistoryModel;
import model.CustomerReportModel;
import model.CustomerListModel;
import model.Employee;
import model.Envelope;
import model.Item;
import model.LoginMod;
import model.User;
import ocsf.server.*;

import java.util.ArrayList;
import java.util.logging.Logger;
import java.sql.*;

import javax.imageio.ImageIO;
import javax.print.DocFlavor.STRING;
/**
 * This class overrides some of the methods in the abstract 
 * superclass in order to give more functionality to the server.
 *
 * @author Dr Timothy C. Lethbridge
 * @author Dr Robert Lagani&egrave;re
 * @author Fran&ccedil;ois B&eacute;langer
 * @author Paul Holden
 * @version July 2000
 */
public class EchoServer extends AbstractServer 
{
// private Hashtable<Integer, Employee> userHash;
 private Connection conn;
 private ServerController controller;


ResultSet rs;
  //Class variables *************************************************

  /**
   * The default port to listen on.
   */
  final public static int DEFAULT_PORT = 5555;
  
  //Constructors ****************************************************
  
  /**
   * Constructs an instance of the echo server.
   *
   * @param port The port number to connect on.
   */
  public EchoServer(int port) 
  {
    super(port);
  }

	public void createTableUser(){
		Statement stmt;
		try {
			stmt = this.getConn().createStatement();
			///////////////*********remember to retrieve*************/////////////
			//stmt.executeUpdate("create table user(username VARCHAR(40),email VARCHAR(40), password int);");
			//stmt.executeUpdate("INSERT INTO user VALUES('A','pit@gmail.com',12344);");
			//stmt.executeUpdate("INSERT INTO user VALUES('CC','pit234@gmail.com',3231);");
		} catch (SQLException e) {	e.printStackTrace();}
	}
  //Instance methods ************************************************
  
  /**
   * This method handles any messages received from the client.
   *
   * @param msg The message received from the client.
   * @param client The connection from which the message originated.
   */
  @SuppressWarnings("unchecked")
public void handleMessageFromClient
    (Object msg, ConnectionToClient client)
  {
	  Employee user = null;
	  Envelope ne=(Envelope) msg;
	
	  try {
		  Statement stmt = conn.createStatement();  
		  if(ne.getTask().equals("searchLogin")) //serch Login
		  {
			  int flag = 0;
			  String re = "SELECT * FROM employee WHERE employee.UserName= '"+((LoginMod)ne.getObject()).getUserName()+"' AND employee.password='"+((LoginMod)ne.getObject()).getPassword()+"'";
			  rs = stmt.executeQuery(re);
			 while(rs.next()){
				 flag++;
				 user = new Employee(rs.getString(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(6));
			 }
			if(flag == 1){
			 client.sendToClient(user);
			}
			else 
				client.sendToClient("Not found User");
		  }
	  
	  if(ne.getTask().equals("updateStatus1")){ //update status to 1 that sign on the user is login to system 
		  String upd = "UPDATE employee SET status= '1' WHERE employee.UserName = '"+((Employee)ne.getObject()).getUsreName()+"' AND employee.password='"+((Employee)ne.getObject()).getUpassword()+"'";
		stmt.executeUpdate(upd);
		controller.SetLog((Employee)ne.getObject(),"login"); //update the login serverLogGui
	       }
	 
	  if(ne.getTask().equals("updateStatus0")){ //logoff
			 
		  String upd = "UPDATE employee SET status= '0' WHERE employee.UserName = '"+((Employee)ne.getObject()).getUsreName()+"' AND employee.password='"+((Employee)ne.getObject()).getUpassword()+"'";
		stmt.executeUpdate(upd);
		controller.SetLog((Employee)ne.getObject(),"logout");  //update the logout serverLogGui
	       }
	   if(ne.getTask().equals("add area")){ //check if exist area and added if Necessary 
		   rs = stmt.executeQuery("SELECT * FROM location WHERE location.area = '"+((String)ne.getObject()).toString()+"'");
		   if(rs.next()){ //this area is exist
			   client.sendToClient("area exist");
		   }
		   else { //add to DB
			   stmt.executeUpdate("INSERT INTO `ccrmdb`.`location` (`area`) VALUES ('"+((String)ne.getObject()).toString()+"'); ");
			   client.sendToClient("area added");
		   }
	     }//if
	   
	   if(ne.getTask().equals("add Category")){ //check if exist Category and added the category if Necessary 
		   rs = stmt.executeQuery("SELECT * FROM category WHERE category.name = '"+((String)ne.getObject()).toString()+"'");
		   if(rs.next()){ //this Category is exist
			   client.sendToClient("Category exist");
		   }
		   else { //add to DB
			   stmt.executeUpdate("INSERT INTO `ccrmdb`.`category` (`name`) VALUES ('"+((String)ne.getObject()).toString()+"'); ");
			   client.sendToClient("Category added");
		   }
	     }//if
	   
	   if(ne.getTask().equals("get area")) {
		   rs = stmt.executeQuery("SELECT area FROM location");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getString(1));
			   i++;
		   } 
		   client.sendToClient(ne);
	   }
	   //
	   
	   if(ne.getTask().equals("get area for employee")) {
		   rs = stmt.executeQuery("SELECT area FROM location");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   int j = 0;
		   while(rs.next()){
			   data.add(rs.getString(1));
			   j++;
		   } 
		   client.sendToClient(ne);
	   }
	   
	   if(ne.getTask().equals("Serch costumer")){  //Search and insert costumer to DB 
		   boolean f1 = false,f2 = false;
		 rs = stmt.executeQuery("SELECT C.id FROM customer AS C WHERE C.id = '"+((CostumerModel)ne.getObject()).getUid()+"'");
          while(rs.next()){ //if id of Costumer is already in DB
        	  f1 = true;
        	  ne.setMess("id exist");
          }
          ResultSet rs1 = stmt.executeQuery("SELECT C.id,U.email FROM customer AS C, user as U WHERE (U.id = C.id AND U.email = '"+((CostumerModel)ne.getObject()).getEmail()+"')");
          while(rs1.next()){//if email of Costumer is already in DB
        	  f2 = true;
        	  if (f1 == true)
        		  ne.setMess("id and email exist");
        	  else 
        		  ne.setMess("email exist");  
          }
          if(f1 == false && f2 == false){
        	  CostumerModel cost = (CostumerModel)ne.getObject();
        	 stmt.executeUpdate("INSERT INTO ccrmdb.user VALUES('"+cost.getUid()+"','"+cost.getFirstname()+"','"+cost.getLasttname()+"','"+cost.getArea()+"','"+cost.getPhone()+"','"+cost.getEmail()+"','"+cost.getAge()+"');");
        	 stmt.executeUpdate("INSERT INTO ccrmdb.customer VALUES('"+cost.getUid()+"','1','1','1');"); //1 mean this is Potential Customer
        	 ne.setMess("Insert Costumer ok");
          }
         
      	client.sendToClient(ne);  
	   }//if
	 
	   if(ne.getTask().equals("Serch employee")){  //Search and insert employee to DB 
		   boolean f1 = false,f2 = false;
		   boolean f3 = false;
		  
		  rs = stmt.executeQuery("SELECT C.id FROM user AS C WHERE C.id = '"+((Employee)ne.getObject()).getUid()+"'");
          if(rs.next()){ //if id of Costumer is already in DB
        	  f1 = true;
        	  ne.setMess("id exist");
          }
        
          ResultSet rs1 = stmt.executeQuery("SELECT U.id,U.email FROM user as U WHERE U.email = '"+((Employee)ne.getObject()).getEmail()+"';");
          if(rs1.next()){//if email of Employee is already in DB
        	  f2 = true;
        	  if (f1 == true)
        		  ne.setMess("id and email exist");
        	  else 
        		  ne.setMess("email exist");  
          }
          rs1 = stmt.executeQuery("SELECT U.UserName FROM employee as U WHERE U.UserName = '"+((Employee)ne.getObject()).getUserName()+"'");
          while (rs1.next())
          {
        	  f3 = true;
        	  if(f1 == true && f2 == true)
        		  ne.setMess("id, email and User name are exist"); 
        	  else
        		  ne.setMess("User name is already exist"); 
          }
          if(f1 == false && f2 == false && f3  == false){
        	  Employee emp = (Employee)ne.getObject();
        	
          stmt.executeUpdate("INSERT INTO ccrmdb.user VALUES('"+emp.getUid()+"','"+emp.getFirstname()+"','"+emp.getLasttname()+"','"+emp.getArea()+"','"+emp.getPhone()+"','"+emp.getEmail()+"','"+emp.getAge()+"');");
          stmt.executeUpdate("INSERT INTO ccrmdb.employee VALUES('"+emp.getUid()+"','0','"+emp.getUserName()+"','"+emp.getUpassword()+"','"+emp.getPosition()+"','0');"); 
          ne.setMess("Insert Employee ok");
          }
         
      	client.sendToClient(ne);  
	   }
	   
	   if(ne.getTask().equals("serch campaign")){
		   int status1=1;
		   rs = stmt.executeQuery("SELECT id FROM campaign WHERE status='"+status1+"'");
		   ArrayList<Integer> data = (ArrayList<Integer>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getInt(1));
			   i++;
		   }
		   if(i>0){
			   ne.setMess("found");
		   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("no campaign");
			   client.sendToClient(ne);
		   }
	   }
	   
	   if(ne.getTask().equals("Potential Customer")){
		   boolean flag  = false;
		   ArrayList<CostumerModel> data = (ArrayList<CostumerModel>)ne.getObject();
		   rs = stmt.executeQuery("SELECT c.id,last_name,first_name,phone,email,area,Age FROM user as u,customer as c WHERE u.id=c.id AND c.raiting = '1' AND c.interest_level = '1' AND c.potential = '0' ");
		   
		   while(rs.next()){
			   flag = true;  //found some customer and change him to potential customer
			   data.add(new CostumerModel(rs.getString(1), rs.getString(2), rs.getString(3),  rs.getString(4),  rs.getString(5),  rs.getString(6),  rs.getInt(7)));
		   }
		   
		   for(int i = 0; i <data.size() ; i++ )
			   stmt.executeUpdate("UPDATE ccrmdb.customer SET potential = '1' WHERE id = '"+data.get(i).getUid()+"'");
		   
           if(flag){
        	   ne.setMess("change to potential"); // found
        	 client.sendToClient(ne);  
           }
           else{
        	   ne.setMess("not found potential"); //not found
        	   client.sendToClient(ne); 
           }
        	   
		   
	   }
	   
	   if(ne.getTask().equals("serch Sales report")){
		   String comments = "";
		   ArrayList<Integer> data = (ArrayList<Integer>)ne.getObject();
		   rs = stmt.executeQuery("SELECT count(event_id),count(DISTINCT c_id),sum(time_call) FROM customer_history AS C WHERE C.cmp_id = '"+data.get(0)+"'");
		   while(rs.next()){
			   for(int  i = 1 ; i< 4 ; i++)
			     data.add(i,rs.getInt(i) ) ;
		   }
		  
		   rs = stmt.executeQuery("SELECT count(event_id) FROM customer_history AS C WHERE C.cmp_id = '"+data.get(0)+"' AND C.quantity = '0' "); 
		   while(rs.next()){
	            	int temp = rs.getInt(1);
	            	data.set(1, data.get(1) - temp); // sub the count of all events from thous then > 0(no buy anyting)	
		   }
		   
		   rs = stmt.executeQuery("SELECT comment FROM customer_history AS C WHERE C.cmp_id = '"+data.get(0)+"' AND C.quantity = '0' ");
		   while(rs.next()){
			   comments+=rs.getString(1)+"\n";
		   }
			   ne.setMess(comments);
			   client.sendToClient(ne);
		   }
	   
       if(ne.getTask().equals("drawData")){  //draw employee data from DB for Human Resources 
    	   String temp = new String();
    	   temp=(String)(ne.getObject());
    	   rs = stmt.executeQuery("SELECT e.id, e.employee_id, e.UserName,e.password,e.position,e.status,u.first_name,u.last_name "
    	   		+ "FROM employee as e,user as u WHERE (e.employee_id='"+temp+"' and u.id = e.id);");
    	  
    	   if (rs.next()){ //employee found
			   Employee emp = new Employee(rs.getString(1),rs.getString(8),rs.getString(7),null,null,null,0,rs.getInt(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(6));
			   //Employee(                       Uid          lasttname     firstname   phone email address Age employee_id   usreName       upassword     Position      status
			   ne.setMess("employee found");
			   ne.setObject(emp);
		   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("employee not found");
			   client.sendToClient(ne);
		   }
       }
       
       if (ne.getTask().equals("updateData")) {
    	   
    	   Employee emp = (Employee)(ne.getObject());
    	   stmt.executeUpdate("UPDATE ccrmdb.employee SET password = '"+emp.getUpassword()+"' WHERE employee_id = '"+emp.getEmployee_id()+"'");
    	   stmt.executeUpdate("UPDATE ccrmdb.employee SET position = '"+emp.getPosition()+"' WHERE employee_id = '"+emp.getEmployee_id()+"'");
    	   stmt.executeUpdate("UPDATE ccrmdb.user SET first_name = '"+emp.getFirstname()+"' WHERE id = '"+emp.getUid()+"'");
    	   stmt.executeUpdate("UPDATE ccrmdb.user SET last_name = '"+emp.getLasttname()+"' WHERE id = '"+emp.getUid()+"'");
    	   
    	   ne.setMess("Update succeeded");
    	   client.sendToClient(ne);

       }
       
       if(ne.getTask().equals("addType")){
    	   //System.out.println("addType");
    	   String type = new String(((ArrayList<String>)(ne.getObject())).get(0));
    	   //System.out.println(type);
    	   String category = new String(((ArrayList<String>)(ne.getObject())).get(1));
    	   //System.out.println(category);
    	   //check if type exists in permissions
    	   
	    	   rs = stmt.executeQuery("SELECT permissions from permissions WHERE permissions='"+type+"'");
	    	   Statement stmt1 = conn.createStatement();
	    	   if(!rs.next()){ //this type does not exist
	    		   String sql1=new String("INSERT INTO ccrmdb.permissions VALUES(NULL,'"+type+"');");
	    		   
					stmt1.executeUpdate(sql1);
				}
	    	 //check if category and type exists in category_to_type
	    	   rs = stmt.executeQuery("SELECT * from category_to_type WHERE (idcategory='"+category+"' and type='"+type+"')");
	    	   if(!rs.next()){ //this type does not exist
	    		   String sql2 = new String ("INSERT INTO ccrmdb.category_to_type VALUES('"+category+"','"+type+"');");
	    		   //System.out.println(sql2);
	    		   stmt1.executeUpdate(sql2);
				   ne.setMess("update commited successfilly");
				   //System.out.println(ne.getTask());
				   client.sendToClient(ne);
				}
	    	   else {
				   ne.setMess("category and type allready exist");
				   client.sendToClient(ne);
	    	   }

       }
       
       
       /*add item to DB */
       if(ne.getTask().equals("add new item")){ //check if exist item
			System.out.println("server resive add new item");
			String str=new String("SELECT * FROM item WHERE item.name = '"+((Item)ne.getObject()).getName()+"' "
					+ "and item.discription= '"+((Item)ne.getObject()).getDiscription()+"' "
							+ "and item.price= '"+Float.toString(((Item)ne.getObject()).getPrice()) +"'");
			System.out.println("serch in DB "+str);
			rs = stmt.executeQuery(str);
			if(rs.next()){ //this item is exist
			   ne.setMess("item exist in DB");
			   client.sendToClient(ne);
			}
			else { //add to DB
				try{
					System.out.println("add image to DB");
					//add image to DB
					String sql=new String("insert into ccrmdb.images (image) values(?)");
					System.out.println("add to DB "+sql);
					PreparedStatement pst = conn.prepareStatement(sql);
					pst.setBytes(1, ((Item)ne.getObject()).getBlob());
					pst.execute();
					//get image id
					sql=new String("SELECT MAX(id) FROM ccrmdb.images;");
					System.out.println("serch in DB image id "+sql);
					rs = stmt.executeQuery(sql);
					int id=0;
					while (rs.next())
						 id =rs.getInt(1);
					System.out.println("the image id is %d "+id);
					//add item to db
					sql=new String("INSERT INTO ccrmdb.item VALUES(NULL,'"+((Item)ne.getObject()).getName()+"','"
							+ ""+((Item)ne.getObject()).getDiscription()+"','"+id+"','"+Float.toString(((Item)ne.getObject()).getPrice()) +"');");
					System.out.println("add to DB "+sql);
					stmt.executeUpdate(sql);
					ne.setMess("item added successfully");
					client.sendToClient(ne);
				}catch(Exception e){
					System.out.println("sql Exception");
				}
			}
	     }//if
       /*get Category for type */
       if(ne.getTask().equals("get Category for type")){
		   rs = stmt.executeQuery("SELECT name FROM category");
		   ArrayList<String> data = new ArrayList<String>();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getString(1));
			   i++;
		   }
		   if(i>0){
			   ne.setMess("found");
			   ne.setObject(data);
			   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("no categories");
			   client.sendToClient(ne);
		   }  
       }
       /*get all items*/
       if(ne.getTask().equals("get items")){
		   rs = stmt.executeQuery("SELECT id FROM item");
		   ArrayList<Integer> data = (ArrayList<Integer>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getInt(1));
			   i++;
		   }
		   if(i>0){
			   ne.setMess("found");
		   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("no items");
			   client.sendToClient(ne);
		   }
	   }
       /*get all areas */
       if(ne.getTask().equals("get areas")){
    	   System.out.println("geting from db all areas");
		   rs = stmt.executeQuery("SELECT * FROM location");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getString(1));
			   i++;
		   }
		   if(i>0){
			   ne.setMess("found areas");
		   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("not found areas");
			   client.sendToClient(ne);
		   }
	   }
       /*get all permissions */
       if(ne.getTask().equals("get all permissions")){
    	   System.out.println("geting from db all permissions");
		   rs = stmt.executeQuery("SELECT permissions FROM ccrmdb.permissions;");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getString(1));
			   i++;
		   }
		   if(i>0){
			   System.out.println("finish ok");
			   ne.setMess("found all permissions");
		   client.sendToClient(ne);
		   }
		   else{
			   System.out.println("finish empty");
			   ne.setMess("not found all permissions");
			   client.sendToClient(ne);
		   }
	   }
       /*add Campaign Pattern to DB */
       if(ne.getTask().equals("set CampaignPatern in DB")){
    	   System.out.println("seting CampaignPatern in DB");
    	   try{// 
	    	   CampaignPatern temp=(CampaignPatern)ne.getObject();//input is good?
	    	   if(temp.getItemid()>0&&temp.getAgefrom()<temp.getAgeto()&&temp.getArea()!=null
	    			   &&temp.getCostumeRrating()>=0&&temp.getCostumeRrating()<=10&&temp.getIntrestLevel()>=0&&temp.getIntrestLevel()<=10
	    			   &&temp.getMessage()!=null&&!(temp.getPermissionArry().isEmpty()))
	    	   {
	    		   String sql=new String("INSERT INTO ccrmdb.campaign VALUES(NULL,'"+temp.getItemid()+"','"+temp.getAgefrom()+"','"
	    				   +temp.getAgeto()+"','"+temp.getArea()+"','"+temp.getCostumeRrating()+"','"+temp.getIntrestLevel()+"','"
	    				   +temp.getMessage()+"','"+0+"');");
					System.out.println("add to DB :  "+sql);
					stmt.executeUpdate(sql);//update db Campaign
					sql=new String("SELECT MAX(id) FROM ccrmdb.campaign;");//get Campaign id 
					System.out.println("serch in DB image id "+sql);
					rs = stmt.executeQuery(sql);
					int cmpid=0;
					while (rs.next())
						 cmpid =rs.getInt(1);
					System.out.println("the CampaignPatern id is %d "+cmpid); 
					for(int i=0 ;i<temp.getPermissionArry().size();i++){//get permissions id and add to campaign permissions
						sql=new String("SELECT id FROM ccrmdb.permissions where permissions='"//add all permissions
								+temp.getPermissionArry().get(i).getName() +"';");
						System.out.println("serch in DB permissions id :"+sql);
						rs = stmt.executeQuery(sql);
						int preid=0;
						while (rs.next())
							preid =rs.getInt(1);
						System.out.println("the permissions id is %d "+preid);//add all permissions in campaign_permissions
						String sql1=new String("INSERT INTO ccrmdb.campaign_permissions VALUES('"+cmpid+"','"+preid+"');");
						System.out.println("add to DB :  "+sql);
						stmt.executeUpdate(sql1);
					}
					ne.setMess("CampaignPatern added successfully");// all good
					client.sendToClient(ne);

	    	   }else{
					ne.setMess("CampaignPatern errore masage");
					System.out.println("CampaignPatern input errore masage");//input error
					client.sendToClient(ne);
			   }
    	   }catch(Exception e){
				ne.setMess("CampaignPatern errore masage");
				System.out.println("CampaignPatern errore masage");//sql error
				client.sendToClient(ne);
    	   }
	   }
       /*get all categoryz */
       if(ne.getTask().equals("get all categoryz")){
    	   System.out.println("geting from db all categoryz");
		   rs = stmt.executeQuery("SELECT * FROM ccrmdb.category;");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   System.out.println(""+ rs.getString(1));
			   data.add(rs.getString(1));
			   i++;
		   }
		   if(i>0){
			   System.out.println("finish ok");
			   ne.setMess("found all categoryz");
		   client.sendToClient(ne);
		   }
		   else{
			   System.out.println("finish empty");
			   ne.setMess("not found all categoryz");
			   client.sendToClient(ne);
		   }
	   }
       /*get all types in gategory */
       if(ne.getTask().equals("get all types in gategory")){
    	   System.out.println("geting from db all types in gategory");
		   ArrayList<String> data = (ArrayList<String>)ne.getObject();
		   rs = stmt.executeQuery("SELECT type FROM ccrmdb.category_to_type where idcategory='"+data.get(0).toString()+"';");
		   data.remove(0);
		   int i = 0;
		   while(rs.next()){
			   System.out.println(""+ rs.getString(1));
			   data.add(rs.getString(1));
			   i++;
		   }
		   if(i>0){
			   System.out.println("finish ok");
			   ne.setMess("found all types in gategory");
		   client.sendToClient(ne);
		   }
		   else{
			   System.out.println("finish empty");
			   ne.setMess("not found types in gategory");
			   client.sendToClient(ne);
		   }
	   }
       /*get all items */
       if(ne.getTask().equals("get items2")){
		   rs = stmt.executeQuery("SELECT id FROM item");
		   ArrayList<Integer> data = (ArrayList<Integer>)ne.getObject();
		   int i = 0;
		   while(rs.next()){
			   data.add(rs.getInt(1));
			   i++;
		   }
		   if(i>0){
			   ne.setMess("found");
		   client.sendToClient(ne);
		   }
		   else{
			   ne.setMess("no items");
			   client.sendToClient(ne);
		   }
	   }
       /*add Item to catalog */
       if(ne.getTask().equals("add Item to catalog")){
    	   System.out.println("add Item to catalog");
    	   try{
    		   ArrayList<String> temp=(ArrayList<String>)ne.getObject();
	    	   if(temp.get(0)!=null&&temp.get(1)!=null&&temp.get(2)!=null)
	    	   {
	    		   String sql=new String("SELECT * FROM ccrmdb.catalog_line where Category='"//test if exist
							+temp.get(0)+"'and type='"+temp.get(1)+"'and item_id='"+temp.get(2)+"';");
					System.out.println("serch in DB catalog_line  :"+sql);
					rs = stmt.executeQuery(sql);
					if (!rs.next()){//if not exist update catalog_line
						sql=new String("INSERT INTO ccrmdb.catalog_line VALUES('"+temp.get(0)+"','"+temp.get(1)+"','"+temp.get(2)+"');");
						System.out.println("add to DB :  "+sql);
						stmt.executeUpdate(sql);
						ne.setMess("Item added successfully to catalog");
						client.sendToClient(ne);
					}
					ne.setMess("item is already exist in catalog");
					client.sendToClient(ne);
	    	   }else{
					ne.setMess("input error");
					System.out.println("input errore");
					client.sendToClient(ne);
			   }
    	   }catch(Exception e){
				ne.setMess("sql Exception");
				System.out.println("sql Exception");
				client.sendToClient(ne);
    	   }
	   }
       
       if(ne.getTask().equals("Customer report")){ 
    	   ArrayList<CustomerReportModel> ar1 = (ArrayList<CustomerReportModel>)ne.getObject();
    	   
    	    stmt.execute("CREATE VIEW v1 AS (SELECT c.c_id,COUNT(c.c_id) as countbuy FROM customer_history as c "
    	   		+ "WHERE c.quantity>0 GROUP BY c.c_id) UNION (SELECT c.c_id,sum(quantity) as countbuy FROM customer_history as c "
        	   		+ " GROUP BY c.c_id HAVING countbuy=0) ORDER BY c_id ");  /*this query count all customer that buy some product  */
    	    
    	 stmt.execute("CREATE VIEW v2 AS (SELECT c.c_id,COUNT(c.c_id) countall FROM customer_history as c "
       	   		+ "GROUP BY c.c_id) ORDER BY c_id");  /*this query count all customer that buy+not buy some product  */
    	 
    	 stmt.execute("CREATE VIEW v3 AS (SELECT v2.c_id, 100*(v1.countbuy/v2.countall) as result FROM v1,v2 WHERE v1.c_id = v2.c_id GROUP BY v2.c_id ORDER BY c_id ASC)");/* */
    	 
    	 stmt.execute("CREATE VIEW v4 AS (SELECT c.c_id,sum(quantity) as sumqua FROM customer_history as c GROUP BY c.c_id  ORDER BY c_id ASC)");
    	 
    	 rs = stmt.executeQuery("SELECT v4.c_id,v4.sumqua,v3.result,u.first_name,u.last_name FROM v3,v4,user as u WHERE v3.c_id = v4.c_id AND v4.c_id = u.id ORDER BY sumqua DESC");
    	  while(rs.next()){
    		  ar1.add(new CustomerReportModel(rs.getInt(1), rs.getInt(2),rs.getDouble(3),rs.getString(4),rs.getString(5) )) ; 
    	   }
    	  stmt.executeUpdate("DROP VIEW `ccrmdb`.`v1`,`ccrmdb`.`v2`,`ccrmdb`.`v3`,`ccrmdb`.`v4`"); //delete all the view
    	  client.sendToClient(ne);
       
       }
//===========================================================
 //pit oh yeee
       if (ne.getTask().equals("SearchHitory"))
       {
    	   int suchCustomer = 0;
    	   String fn= null,ln = null;
    	   String CID = (String)ne.getObject();
    	   String re1 = "SELECT first_name,last_name FROM user AS U WHERE U.id = '"+CID+"'";
    	   rs = stmt.executeQuery(re1);
    	   while(rs.next()){
    		   suchCustomer = 1;
    		   if (suchCustomer == 1)
    		   {
    		   fn = rs.getString(1);
    		   ln = rs.getString(2);
    		   }
    	   }
    	   if (suchCustomer == 1)
    	   {
           CustomerHistoryModel  custHistoryMod = new CustomerHistoryModel(fn,ln,CID);
    	   String re = "SELECT cmp_id,quantity,date,comment,time_call FROM customer_history AS H WHERE H.c_id = '"+CID+"'";
    	   rs = stmt.executeQuery(re);
    	   while (rs.next())
    	   {
    		   custHistoryMod.SetHistory(rs.getString(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getInt(5));
    	   }
    	   client.sendToClient(custHistoryMod);
    	   }
    	   else 
    	   {
    		   client.sendToClient("no such a customer");
    	   }
       }
       if (ne.getTask().equals("Take Campain")){
    	 
    	   int statusOK = 1;
    	   String result = new String();
    	   result ="";
    	   String re = "SELECT id FROM campaign AS C WHERE C.status = '"+statusOK+"'";
    	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   result += rs.getString(1);
    		   result +=",";
    	   }
    	
    	   ne.setObject((String)result);
    	   client.sendToClient(ne);
       }
       if (ne.getTask().equals("Take a Customer List"))
       {
    	   int flag1 = 0;
    	   int flag2 = 0;
    	   int flag3 = 0;
    	   String result =new String();
    	   result = "";
    	   String finresult =new String();
    	   finresult = "";
    	   String nC = new String();
    	  nC = ((CustomerListModel)ne.getObject()).getCampID();
    	   String re = "SELECT message FROM campaign AS C WHERE C.id = '"+nC+"'";
    	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   ((CustomerListModel)ne.getObject()).setDescibe(rs.getString(1)); 	   
    		   }
    	    re = "SELECT customer_id FROM customers_in_campaign_to_salesman AS C WHERE C.campaign_id = '"+nC+"'";
    	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   result += rs.getString(1);
    		   result +=",";    	   
    		   }
      	String[] date1=((CustomerListModel)ne.getObject()).getCurrDate().trim().split("/");
      	int year = Integer.parseInt(date1[0]);
      	int month = Integer.parseInt(date1[1]);
      	int day = Integer.parseInt(date1[2]);
    	day -=14;
    	if (day<= 0)
    	{
    		day = 30+day;
    		month--;
    		if (month== 0)
    		{
    			month = 12;
    			year--;
    		}
    		
    	}
 	
   		String[] q=result.trim().split(",");
   		for (int i=0;i< q.length ;i++)
   		{
 		  flag1 = 0;
 		  flag2 = 0;
 		  flag3 = 0;
 		   System.out.println(q[i]);

     	   re = "SELECT c_id FROM customer_history AS C WHERE C.cmp_id = '"+nC+"' AND C.c_id = '"+q[i]+"'";
     	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   flag1++;    		   
    		   } 
 		   System.out.println(flag1);
      	   re = "SELECT date FROM customer_history AS C WHERE C.cmp_id <> '"+nC+"' AND C.c_id = '"+q[i]+"'";
     	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		 
    		   flag2 = 1;
    		   result = rs.getString(1);
    		   String[] q1=result.trim().split("/");
    	      	int year1 = Integer.parseInt(q1[2]);
    	      	int month1 = Integer.parseInt(q1[1]);
    	      	int day1 = Integer.parseInt(q1[0]);
    	    	if ( year > year1 ){  
    	    		flag3 = 1;
    	    	}
    	      	if ( year == year1 ){  
    	      		if (month > month1){    	     
    	      			if (flag1 == 0)
    	      			{
    	      				flag3 = 1;
    	      			}
    	      		}
    	      		if (month == month1 && day >= day1)
    	      		{
      	      			if (flag1 == 0)
    	      			{
      	      					flag3 = 1;
    	      			}
    	      		}
    	      	}
    		   } 
    	   if ((flag1 == 0 && flag2 == 0) || (flag1 == 0 && flag3 == 1))
    	   {
    		   ResultSet aa;
          	   re = "SELECT first_name,last_name  FROM user AS C WHERE C.id = '"+q[i]+"'";
         	   aa = stmt.executeQuery(re);
             while(aa.next()){
       		   ((CustomerListModel)ne.getObject()).getCD().add(new User(q[i],aa.getString(1),aa.getString(2)));	 
         	  }
    	   }
   			
   		}
    	   client.sendToClient(ne);
       }
       
       if(ne.getTask().equals("update customer history")){
    	   CustomerHistoryModel upD = (CustomerHistoryModel)ne.getObject();
    	   int id = Integer.parseInt(upD.getCID());
    	   int ccmmp =Integer.parseInt(upD.getDetailHis().get(0).cmp_id);
    	   
    	   
    	   String re = "INSERT INTO ccrmdb.customer_history VALUES(NULL,'"+id+"','"+ccmmp+
    			   "','"+upD.DetailHis.get(0).Qu+"','"+upD.DetailHis.get(0).Date1+"','"+upD.DetailHis.get(0).Comments+"','"+upD.DetailHis.get(0).time_call+"');";
    	  stmt.executeUpdate(re);
    	   	    	   
       }
       if (ne.getTask().equals("take all prem"))
       {
    	   String idu = new String();
    	   String finRS = new String();
    	   finRS = "";
    	   idu = (String)ne.getObject();
    	   String prem = new String();
    	   prem = "";
    	   String re = "Select permissions FROM customer_permissions AS C WHERE C.customer_id = '"+idu+"'";
    	   rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   prem += rs.getString(1);
    		   prem+="," ;
    	   }
    	    System.out.println(prem);
      		String[] q=prem.trim().split(",");
     	    re = "SELECT * FROM ccrmdb.permissions;";
     	    rs = stmt.executeQuery(re);
    	   while(rs.next()){
    		   int flag  = 0;
    		   System.out.println("==========================");
    	       System.out.println(rs.getInt(1)+"  "+ rs.getString(2)); 
   	     	   System.out.println("==========================");
    		   for (int i = 0 ; i< q.length;i++)
    		   {
    			   if (q[i].equals(rs.getString(1)))
    			   {
    				   flag = 1;
    			   }
    		   }
    		   if (flag == 0 )
    		   {
    			   finRS += rs.getString(2);
    			   finRS +=",";
    		   }

    	   }
    	   System.out.println(finRS);
    	   ne.setObject(finRS);
    	   client.sendToClient(ne);
       }
       
       if (ne.getTask().equals("update the prem user"))
       {
    	  String idu = (String)ne.getObject();
    		String[] q=idu.trim().split(",");
    		//int k = Integer.parseInt(q[0]);
    	//	int j = Integer.parseInt(q[1]);  
    		  String re = "Select id FROM permissions AS P WHERE P.permissions = '"+q[0]+"'";
    	   	   rs = stmt.executeQuery(re);
    	   	   while(rs.next()){
    	   		   q[0] = rs.getString(1);
    	   	   }
    	   	   
    		 re = "INSERT INTO customer_permissions VALUES('"+q[1]+"','"+q[0]+"')";
    	  	 stmt.executeUpdate(re);
      	   client.sendToClient("OK");
       }
       if (ne.getTask().equals("analyze now")){
    	   String cus =  new String();
    	   cus = "";
 		  String re = "Select id FROM customer";
	   	   rs = stmt.executeQuery(re);
	   	   while(rs.next())
	   	   {
	   		   cus += rs.getString(1);
	   		   cus +=",";
	   	   }
    	   System.out.println(cus);
	   	   String[] q=cus.trim().split(",");
	   	   
	   	   
	   	   
    	   for (int i= 0 ; q.length > i; i++)
    	   {
    	
    		   Statement stmt1 = conn.createStatement();  
    			ResultSet rs2;
    			ResultSet rs1;
    		   int sum = 0;
    	 		   re = "Select cmp_id,quantity FROM customer_history  AS C WHERE C.c_id='"+q[i]+"'";
    		   	   rs = stmt.executeQuery(re);
    		   	 
    		   	   while (rs.next())
    		   	   {  		   
    		   		 int g =  rs.getInt(2);
    		   		 int pp = 0;

    		   		   re = "Select Itm_Id FROM campaign  AS A WHERE A.id='"+rs.getString(1)+"'";
    		   		   rs1 =  stmt1.executeQuery(re);

    		   		   while(rs1.next())
    		   		   {
    		   		
     		   		   re = "Select price FROM item  AS I WHERE I.id='"+rs1.getString(1)+"'";
    		   		   }
     		   		   rs2 =  stmt1.executeQuery(re);

    		   		   
     		   		  while(rs2.next())
     		   		  {
     		   			  int h;
     		   			  h =rs2.getInt(1);
     		   			  pp = h*g;
     		   			  sum = sum +pp;
     		   		  }
    		   }
    		   	   
    		   	
    		   	   int rating = 1;
    		   	   if (sum >= 0 && sum <= 3)
    		   	   {
    		   		   rating = 1;
    		   	   }
    		   	   else if (sum >= 4 && sum <= 7)
    		   	   {
    		   		   rating = 2;
    		   	   }
    		   	   else if (sum >= 8 && sum <= 10)
    		   	   {
    		   		   rating = 3;
    		   	   }
    		   	   else if (sum >= 11 && sum <= 14)
    		   	   {
    		   		   rating = 4;
    		   	   }
    		   	   else if (sum >= 14 && sum <= 30)
    		   	   {
    		   		   rating = 5;
    		   	   }
    		   	   else if (sum >= 31 && sum <= 60)
    		   	   {
    		   		   rating = 6;
    		   	   }
    		   	   else if (sum >= 61 && sum <= 90)
    		   	   {
    		   		   rating = 7;
    		   	   }
    		   	   else if (sum >= 91 && sum <= 130)
    		   	   {
    		   		   rating = 8;
    		   	   }
    		   	   else if (sum >= 131 && sum <= 190)
    		   	   {
    		   		   rating = 9;
    		   	   }
    		   	   else if (sum >= 191)
    		   	   {
    		   		   rating = 10;
    		   	   }
    		   
    		   	stmt.executeUpdate("UPDATE ccrmdb.customer SET raiting = '"+rating+"' WHERE id = '"+q[i]+"'");    	
    		   	
           	float succ = 1,error = 1;
    		int interst = 1;
 	 		   re = "Select quantity FROM customer_history  AS C WHERE C.c_id='"+q[i]+"'";
 	 		 rs = stmt.executeQuery(re);
 	 		 while (rs.next())
 	 		 {
 	 			 if (rs.getInt(1) == 0)
 	 			 {
 	 				 error++;
 	 			 }
 	 			 else
 	 			 {
 	 				 succ++;
 	 			 }
 	 		 }
 	 		 if ((succ/error)<1)
 	 		 {
 	 			 if ((succ/error)<=0.2)
 	 			 {
 	 				interst = 1;
 	 			 }
 	 			 else  if ((succ/error)<=0.4 && (succ/error)>0.2)
 	 			 {
 	 				interst = 2;
 	 			 }
 	 			 else  if ((succ/error)<=0.7 && (succ/error)>0.4)
 	 			 {
 	 				interst = 3;
 	 			 }
 	 			 else  if ((succ/error)<1 && (succ/error)>0.7)
 	 			 {
 	 				interst = 4;
 	 			 }	 
 	 		 }
 	 		 else if ((succ/error) == 1)
 	 		 {
 	 			interst = 5;
 	 		 }
 	 		 else if ((succ/error)>1)
 	 		 {
 	 			 if ((succ/error)<=2 && (succ/error)>1)
 	 			 {
 	 				interst = 6;
 	 			 }
 	 			 else  if ((succ/error)<=3 && (succ/error)>2)
 	 			 {
 	 				interst = 7;
 	 			 }
 	 			 else  if ((succ/error)<=5 && (succ/error)>3)
 	 			 {
 	 				interst = 8;
 	 			 }
 	 			 else  if ((succ/error)<=10 && (succ/error)>5)
 	 			 {
 	 				interst = 9;
 	 			 }	 
 	 			 else  if ((succ/error)<15 && (succ/error)>10)
 	 			 {
 	 				interst = 10;
 	 			 }	 
    	   }
 		   	stmt.executeUpdate("UPDATE ccrmdb.customer SET interest_level = '"+interst+"' WHERE id = '"+q[i]+"'"); 
        }
    	   
       }
       
       if (ne.getTask().equals("take all unactive campign")){
    	   
    	   		int status0 = 0;
	 		   String re = "Select id FROM campaign  AS C WHERE C.status='"+status0+"'";
	 		   rs = stmt.executeQuery(re);
	 		   String RRR=new String();
	 			RRR = "";
	 		   while (rs.next())
	 		   {
	 			   RRR += rs.getString(1);
	 			   RRR += ",";
	 		   }
	 		  ne.setObject((String)RRR);
	    	   client.sendToClient(ne);
       }
       if (ne.getTask().equals("Active the campign plzz")){
    	   String cmp = new String();
    	   cmp =(String)ne.getObject();
    	   int pos = 3;
 		   String re = "Select id FROM employee  AS E WHERE E.Position='"+pos+"'";
 		   rs = stmt.executeQuery(re);
 		   String emp = new String();
 		   emp ="";
 		   while (rs.next())
 		   {
 			   emp+=rs.getString(1);
 			   emp+=",";
 		   }
	   	   String[] emplo=emp.trim().split(",");
	   	   int fromAge=0;
	   	   int toAge=0;
	   	   String Area="";
	   	   int intertL=0;
	   	   int rateL=0;
 		   re = "Select * FROM campaign  AS C WHERE C.id='"+cmp+"'";
 		   rs = stmt.executeQuery(re);
 		   while(rs.next())
 		   {
 			  fromAge = rs.getInt(3);
 			  toAge = rs.getInt(4);
 			 Area = rs.getString(5);
 			 rateL = rs.getInt(6);
 			 intertL = rs.getInt(7);
 		   }
 		   String customers = new String();
 		  customers="";
 		  re = "SELECT C.id FROM ccrmdb.customer AS C,ccrmdb.user AS D WHERE C.id=D.id AND C.raiting>="+rateL+" AND C.interest_level>="+intertL+" AND D.Age>="+fromAge+" AND D.Age<="+toAge+" AND D.area ='"+Area+"'";
		   rs = stmt.executeQuery(re);
		   while (rs.next())
		   {
			   customers+=rs.getString(1);
			   customers+= ",";
		   }
		   
		   re = "SELECT permissions FROM campaign_permissions AS C WHERE campaign_id = '"+cmp+"'";
		   rs = stmt.executeQuery(re);
		   String prm = "";
		   while (rs.next())
		   {
			   prm+=rs.getString(1);
			   prm+= ",";
		   }
	   	   String[] prem=prm.trim().split(",");
	   	   String[] custo=customers.trim().split(",");
	   	   customers = "";
 		   for (int i =0;i<custo.length;i++)
 		   {
 			   int flag =0;
 			   System.out.println(custo[i]);
 			   prm = "";
 			  re = "SELECT permissions FROM customer_permissions AS C WHERE C.customer_id = '"+custo[i]+"'";
 			  rs = stmt.executeQuery(re);
 			   while (rs.next())
 			   {
 				   prm+=rs.getString(1);
 				   prm+= ",";
 			   }
 		   	   String[] custo_prem=prm.trim().split(",");
 		   	   for (int j=0; j<custo_prem.length; j++)
 		   	   {
 		   		   for (int k=0;k<prem.length;k++)
 		   		   {
 		   			   if (prem[k].equals(custo_prem[j]))
 		   			   {
 		   				   flag=1;
 		   			   }
 		   		   }
 		   	   }
 		   	   if (flag==1)
 		   	   {
 		   		customers+=custo[i];
 		   		customers+=",";
 		   	   }
 		   }
 		   System.out.println(customers);
	   	   String[] finRes=customers.trim().split(",");
	   	   int kk=0;
	   	   if ((customers.equals(""))==false)
	   	   {
	   	   for (int jj = 0 ;jj<finRes.length;jj++)
	   	   {
	   		  
	   		  re = "INSERT INTO ccrmdb.customers_in_campaign_to_salesman VALUES('"+cmp+"','"+finRes[jj]+"','"+emplo[kk]+"');";
	    	  	 stmt.executeUpdate(re);
	    	  	 kk++;
	    	  	 kk = kk%(emplo.length);
	   	   }
	   	   int status1 = 1;
		   	stmt.executeUpdate("UPDATE ccrmdb.campaign SET status = '"+status1+"' WHERE id = '"+cmp+"'");  
		   	ne.setMess("all good123");
	   	   client.sendToClient(ne);
       }
	   	   else{
	   		  int status1 = 1;
			   	stmt.executeUpdate("UPDATE ccrmdb.campaign SET status = '"+status1+"' WHERE id = '"+cmp+"'");  
	   		   ne.setMess("no customer");
	   		 client.sendToClient(ne);
	   	   }
	   	
	 }
       
       
     //===========================================================
       
	   
	  }//end try
	  
          catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
	     // TODO Auto-generated catch block
	     e.printStackTrace();
         }	  
  }
  
  
  public Connection getConn() {
	return conn;
}
  
 
 public void setConn(Connection conn) {
	this.conn = conn;
}
 public void setController(ServerController controller) {
		this.controller = controller;
	}

  
/**
   * This method overrides the one in the superclass.  Called
   * when the server starts listening for connections.
   */
  protected void serverStarted()
  {
    System.out.println
      ("Server listening for connections on port " + getPort());
  }
  
  /**
   * This method overrides the one in the superclass.  Called
   * when the server stops listening for connections.
   */
  protected void serverStopped()
  {
    System.out.println
      ("Server has stopped listening for connections.");
  }
  
}
//End of EchoServer class   
